﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TeacherHelp.DAL
{
    public class SubjectsService
    {
        public string GetSubjectsInfoSQL()
        {
            string sql = @"
            select 
                Id,
                [Name] 
            from Subjects";
            return sql;
        }

        public string GetInsertSubjectSQL(string name)
        {
            string sql = $"insert Subjects([Name]) values('{name}');";
            return sql;
        }

        public string GetDelSubjectSQL(string id)
        {
            string sql = $"delete from Subjects where Id = {id};";
            return sql;
        }

        public string GetSubjectCountSQL(string name)
        {
            string sql = $"select count(0) from Subjects where [Name] = '{name}';";
            return sql;
        }

        public string GetUpdateSubjectSQL(Model.Subjects subjects)
        {
            string sql = $"update subjects set [Name] = '{subjects.Name}' where Id = {subjects.Id};";
            return sql;
        }

        public string GetSubjectsCount50SQL() {
            string sql = $@"
            select a.Id,
                   a.[Name] 
            from subjects a
            inner join Exercises b on a.Id = b.SubjectsId
            group by a.Id,a.[Name]
            having count(b.Id)>=50";
            return sql;
        }
    }
}
